package com.wmw.Utility;

import java.io.Serializable;

import com.wmw.DataPool.OrdersForm;
import com.wmw.DataPool.OrdersFormItem;

import android.content.Context;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBConstant {
	public static final String _DATABASE_NAME = "wmwDB.db";
	public static final int _DATABASE_VERSION = 1;
	
	public static final String _HistoryFormTableName = "history_form";
	public static final String _HFTN_ID = "id";
	public static final String _HFTN_TotalPrice = "total_price";
	public static final String _HFTN_PhoneNumber = "phone_number";
	public static final String _HFTN_Address = "address";
	public static final String _HFTN_TotalNum = "total_num";
	public static final String _HFTN_Time = "time";
	public static final String _HFTN_Remark = "remark";
	
	public static final String _HistoryFormItemTableName = "history_form_item";
	public static final String _HFITN_OrderFormItemID = "id";
	public static final String _HFITN_Name = "name";
	public static final String _HFITN_UnitPrice = "unit_price";
	public static final String _HFITN_TotalNum = "total_num";
	public static final String _HFITN_BelongOrderForm = "belong_order_form";
	
	public static final String _OrderFormTableName = "order_form";
	public static final String _OFTN_ID = "id";
	public static final String _OFTN_TotalPrice = "total_price";
	public static final String _OFTN_PhoneNumber = "phone_number";
	public static final String _OFTN_Address = "address";
	public static final String _OFTN_TotalNum = "total_num";
	public static final String _OFTN_Time = "time";
	public static final String _OFTN_Remark = "remark";
	
	public static final String _OrderFormItemTableName = "order_form_item";
	public static final String _OFITN_OrderFormItemID = "id";
	public static final String _OFITN_Name = "name";
	public static final String _OFITN_UnitPrice = "unit_price";
	public static final String _OFITN_TotalNum = "total_num";
	public static final String _OFITN_BelongOrderForm = "belong_order_form";
	
	public static final String _ClassifyTableName = "classify";
	public static final String _CTN_CLASSIFY_ID = "id";
	public static final String _CTN_CLASSIFY_NAME = "classify_name";
	public static final String _CTN_TOTAL_SALES = "total_sales";
	
	public static final String _DishTableName = "dish";
	public static final String _DTN_DishID = "id";
	public static final String _DTN_DishName = "dish_name";
	public static final String _DTN_Price = "price";
	public static final String _DTN_TotalSales = "total_sales";
	public static final String _DTN_SaleState = "sale_state";
	public static final String _DTN_LeftNum = "left_num";
	public static final String _DTN_PositiveCommentCnt = "positive_comment_cnt";
	public static final String _DTN_ModerateCommentCnt = "moderate_comment_cnt";
	public static final String _DTN_NegativeCommentCnt = "negative_comment_cnt";
	public static final String _DTN_BelongClassify = "belong_classify";
	

	// 建立一个内部类,主要生成一个数据库
	public static class DatabaseHelper extends SQLiteOpenHelper implements Serializable{

		/**
		 * 
		 */
		private static final long serialVersionUID = 4240191558887370304L;

		public DatabaseHelper(Context context) {
			super(context, DBConstant._DATABASE_NAME, null, 1);
		}

		// 在数据库第一次生成的时候会调用这个方法，一般我们在这个方法里边生成数据库表。
		@Override
		public void onCreate(SQLiteDatabase db) {
			//
			// String sql = "CREATE TABLE " + TABLE_NAME + " (" + TITLE
			// + " text not null, " + BODY + " text not null " + ");";
			// Log.i("haiyang:createDB=", sql);
			// db.execSQL(sql);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		}
	}
	
	public static long countTableRecordsNum(SQLiteDatabase db, String tableName) {
		// long numRows = DatabaseUtils.longForQuery(db, "SELECT COUNT(*) FROM "
		// + tableName,null); can do as well.
		long numRows = DatabaseUtils.queryNumEntries(db, tableName);
		return numRows;
	}
	public static void createHistoryTables(SQLiteDatabase db) {
		// CREATE TABLE IF NOT EXISTS order_form(order_form_id INTEGER PRIMARY
		// KEY
		// , total_price REAL, phone_number TEXT, address TEXT,
		// total_num INTEGER, time DATETIME, remark TEXT);
		String orderFormSQL = "CREATE TABLE IF NOT EXISTS "
				+ DBConstant._HistoryFormTableName + " (" + DBConstant._HFTN_ID
				+ " INTEGER PRIMARY KEY," + DBConstant._HFTN_TotalPrice
				+ " REAL, " + DBConstant._HFTN_PhoneNumber + " TEXT, "
				+ DBConstant._HFTN_Address + " TEXT, "
				+ DBConstant._HFTN_TotalNum + " TEXT, " + DBConstant._HFTN_Time
				+ " DATETIME, " + DBConstant._HFTN_Remark + " TEXT" + ");";
		Log.d(Common.GetLineInfo(), orderFormSQL);
		try {
			db.execSQL(orderFormSQL);
			Log.d(Common.GetLineInfo(), "Create table success");
		} catch (SQLException e) {
			Log.e(Common.GetLineInfo(),
					"Create table failure -" + e.getStackTrace());
		}

		// CREATE TABLE IF NOT EXISTS order_form_item(order_form_item_id integer
		// PRIMARY KEY
		// AUTOINCREMENT, name TEXT UNIQUE, unit_price REAL, total_num INTEGER,
		// belong_order_form INTEGER, FOREIGN KEY(belong_order_form) REFERENCES
		// order_form(order_form_id) ON DELETE CASCADE);
		String dishSQL = "CREATE TABLE  IF NOT EXISTS "
				+ DBConstant._HistoryFormItemTableName + " ("
				+ DBConstant._HFITN_OrderFormItemID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ DBConstant._HFITN_Name + " TEXT, "
				+ DBConstant._HFITN_UnitPrice + " REAL, "
				+ DBConstant._HFITN_TotalNum + " INTEGER, "
				+ DBConstant._HFITN_BelongOrderForm + " INTEGER REFERENCES "
				+ DBConstant._HistoryFormTableName + "(" + DBConstant._HFTN_ID
				+ ") ON DELETE CASCADE);";

		Log.d(Common.GetLineInfo(), dishSQL);
		try {
			db.execSQL(dishSQL);
			Log.d(Common.GetLineInfo(), "Create table success");
		} catch (SQLException e) {
			Log.e(Common.GetLineInfo(), "Create table failure");
		}

	}
	

	public static void createOrdersTables(SQLiteDatabase db) {
		// mOpenHelper.getWritableDatabase()语句负责得到一个可写的SQLite数据库，如果这个数据库还没有建立，
		// 那么mOpenHelper辅助类负责建立这个数据库。如果数据库已经建立，那么直接返回一个可写的数据库。

		// CREATE TABLE IF NOT EXISTS classify(id INTEGER PRIMARY KEY AUTOINCREMENT,
		// classify_name TEXT UNIQUE, total_sales INTEGER);
		String classifySQL = "CREATE TABLE IF NOT EXISTS "
				+ DBConstant._ClassifyTableName + " ("
				+ DBConstant._CTN_CLASSIFY_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ DBConstant._CTN_CLASSIFY_NAME + " TEXT UNIQUE, "
				+ DBConstant._CTN_TOTAL_SALES + " INTEGER" + ");";
		Log.d(Common.GetLineInfo(), classifySQL);
		try {
			db.execSQL(classifySQL);
			Log.d(Common.GetLineInfo(), "Create table success");
		} catch (SQLException e) {
			Log.e(Common.GetLineInfo(),
					"Create table failure -" + e.getStackTrace());
		}

		// CREATE TABLE IF NOT EXISTS dish(dish_id integer PRIMARY KEY
		// AUTOINCREMENT,
		// dish_name TEXT UNIQUE, price REAL, total_sales
		// INTEGER, sale_state INTEGER, left_num INTEGER, positive_comment_cnt
		// INTEGER, moderate_comment_cnt INTEGER, negative_comment_cnt INTEGER,
		// belong_classify INTEGER, FOREIGN KEY(belong_classify) REFERENCES
		// classify(classify_id));
		String dishSQL = "CREATE TABLE IF NOT EXISTS "
				+ DBConstant._DishTableName + " (" + DBConstant._DTN_DishID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ DBConstant._DTN_DishName + " TEXT UNIQUE, "
				+ DBConstant._DTN_Price + " REAL, "
				+ DBConstant._DTN_TotalSales + " INTEGER, "
				+ DBConstant._DTN_SaleState + " INTEGER, "
				+ DBConstant._DTN_LeftNum + " INTEGER, "
				+ DBConstant._DTN_PositiveCommentCnt + " INTEGER, "
				+ DBConstant._DTN_ModerateCommentCnt + " INTEGER, "
				+ DBConstant._DTN_NegativeCommentCnt + " INTEGER, "
				+ DBConstant._DTN_BelongClassify + " INTEGER REFERENCES "
				+ DBConstant._ClassifyTableName + "("
				+ DBConstant._CTN_CLASSIFY_ID + ") ON DELETE CASCADE);";

		Log.d(Common.GetLineInfo(), dishSQL);
		try {
			db.execSQL(dishSQL);
			Log.d(Common.GetLineInfo(), "Create table success");
		} catch (SQLException e) {
			Log.e(Common.GetLineInfo(), "Create table failure");
		}
		
		// Create comments tables.
	}

	public static boolean insertOneOrderFormItemToHistoryDB(SQLiteDatabase db, OrdersFormItem item, int id) {
		boolean success = false;
		String sql1 = "insert into " + DBConstant._HistoryFormItemTableName
				+ " (" + DBConstant._HFITN_Name + ", "
				+ DBConstant._HFITN_UnitPrice + ", "
				+ DBConstant._HFITN_TotalNum + ", "
				+ DBConstant._HFITN_BelongOrderForm + ") values('"
				+ item.get_name() + "', " + item.get_unitPrice() + ", "
				+ item.get_totalNum() + ", " + id + ");";
		try {
			Log.d(Common.GetLineInfo(), "sql -" + sql1);
			db.execSQL(sql1);
			Log.d(Common.GetLineInfo(), "insert into database success");
			success = true;
		} catch (SQLException e) {
			Log.e(Common.GetLineInfo(),
					"insert into database failure" + e.getStackTrace());
		}

		return success;
	}

	public static boolean insertOneOrderFormToHistoryDB(SQLiteDatabase db, OrdersForm of) {
		boolean success = false;
		// time format: 2009-03-20 11:00:00
		String sql1 = "insert into " + DBConstant._HistoryFormTableName
				+ " values(" + of.get_id() + ", " + of.get_totalPrice() + ", '"
				+ of.get_phoneNumber() + "', '" + of.get_address() + "', "
				+ of.get_totalNum() + ", datetime('" + of.get_time() + "'), '"
				+ of.get_remark() + "');";
		try {
			Log.d(Common.GetLineInfo(), "sql -" + sql1);
			db.execSQL(sql1);
			Log.d(Common.GetLineInfo(), "insert into database success");
			success = true;
		} catch (SQLException e) {
			Log.e(Common.GetLineInfo(),
					"insert into database failure" + e.getStackTrace());
		}

		return success;
	}

}
